const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const fs = require('fs');

class DatabaseManager {
  constructor() {
    this.dbPath = path.join(__dirname, '../../data/ai2word.db');
    this.db = null;
  }

  initialize() {
    return new Promise((resolve, reject) => {
      // 确保数据目录存在
      const dataDir = path.dirname(this.dbPath);
      if (!fs.existsSync(dataDir)) {
        fs.mkdirSync(dataDir, { recursive: true });
      }

      this.db = new sqlite3.Database(this.dbPath, (err) => {
        if (err) {
          console.error('数据库连接失败:', err);
          reject(err);
        } else {
          console.log('数据库连接成功');
          this.createTables().then(resolve).catch(reject);
        }
      });
    });
  }

  createTables() {
    return new Promise((resolve, reject) => {
      const tables = [
        // 文章表
        `CREATE TABLE IF NOT EXISTS articles (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          title TEXT NOT NULL,
          content TEXT NOT NULL,
          topic TEXT,
          word_count INTEGER,
          style TEXT,
          length TEXT,
          keywords TEXT,
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
          updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )`,
        
        // 主题表
        `CREATE TABLE IF NOT EXISTS topics (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          title TEXT NOT NULL,
          category TEXT,
          source TEXT, -- manual, ai_generated, rss
          used_count INTEGER DEFAULT 0,
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )`,
        
        // 设置表
        `CREATE TABLE IF NOT EXISTS settings (
          key TEXT PRIMARY KEY,
          value TEXT NOT NULL,
          updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )`,
        
        // 模板表
        `CREATE TABLE IF NOT EXISTS templates (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          name TEXT NOT NULL,
          content TEXT NOT NULL,
          type TEXT, -- article, prompt
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )`
      ];

      let completed = 0;
      const total = tables.length;

      tables.forEach((sql) => {
        this.db.run(sql, (err) => {
          if (err) {
            console.error('创建表失败:', err);
            reject(err);
          } else {
            completed++;
            if (completed === total) {
              console.log('数据库表创建完成');
              this.initializeDefaultData().then(resolve).catch(reject);
            }
          }
        });
      });
    });
  }

  initializeDefaultData() {
    return new Promise((resolve, reject) => {
      // 插入默认设置
      const defaultSettings = [
        ['ai_provider', 'openai'],
        ['api_key', ''],
        ['api_base_url', ''],
        ['default_style', 'professional'],
        ['default_length', 'normal'],
        ['auto_save', 'true'],
        ['theme', 'light']
      ];

      let completed = 0;
      const total = defaultSettings.length;

      defaultSettings.forEach(([key, value]) => {
        this.db.run(
          'INSERT OR IGNORE INTO settings (key, value) VALUES (?, ?)',
          [key, value],
          (err) => {
            if (err) {
              console.error('插入默认设置失败:', err);
            }
            completed++;
            if (completed === total) {
              resolve();
            }
          }
        );
      });
    });
  }

  // 文章相关方法
  saveArticle(article) {
    return new Promise((resolve, reject) => {
      const {
        title,
        content,
        topic,
        wordCount,
        style,
        length,
        keywords
      } = article;

      const keywordsStr = Array.isArray(keywords) ? keywords.join(',') : keywords || '';

      this.db.run(
        `INSERT INTO articles (title, content, topic, word_count, style, length, keywords)
         VALUES (?, ?, ?, ?, ?, ?, ?)`,
        [title, content, topic, wordCount, style, length, keywordsStr],
        function(err) {
          if (err) {
            console.error('保存文章失败:', err);
            reject(err);
          } else {
            resolve({ id: this.lastID, success: true });
          }
        }
      );
    });
  }

  getArticleHistory(limit = 50, offset = 0) {
    return new Promise((resolve, reject) => {
      this.db.all(
        `SELECT id, title, topic, word_count, style, length, created_at, updated_at
         FROM articles 
         ORDER BY created_at DESC 
         LIMIT ? OFFSET ?`,
        [limit, offset],
        (err, rows) => {
          if (err) {
            console.error('获取文章历史失败:', err);
            reject(err);
          } else {
            resolve(rows);
          }
        }
      );
    });
  }

  getArticleById(id) {
    return new Promise((resolve, reject) => {
      this.db.get(
        'SELECT * FROM articles WHERE id = ?',
        [id],
        (err, row) => {
          if (err) {
            console.error('获取文章失败:', err);
            reject(err);
          } else {
            resolve(row);
          }
        }
      );
    });
  }

  updateArticle(id, updates) {
    return new Promise((resolve, reject) => {
      const fields = Object.keys(updates);
      const values = Object.values(updates);
      const setClause = fields.map(field => `${field} = ?`).join(', ');

      this.db.run(
        `UPDATE articles SET ${setClause}, updated_at = CURRENT_TIMESTAMP WHERE id = ?`,
        [...values, id],
        function(err) {
          if (err) {
            console.error('更新文章失败:', err);
            reject(err);
          } else {
            resolve({ success: true, changes: this.changes });
          }
        }
      );
    });
  }

  deleteArticle(id) {
    return new Promise((resolve, reject) => {
      this.db.run(
        'DELETE FROM articles WHERE id = ?',
        [id],
        function(err) {
          if (err) {
            console.error('删除文章失败:', err);
            reject(err);
          } else {
            resolve({ success: true, changes: this.changes });
          }
        }
      );
    });
  }

  // 主题相关方法
  saveTopic(topic) {
    return new Promise((resolve, reject) => {
      const { title, category, source } = topic;

      this.db.run(
        'INSERT INTO topics (title, category, source) VALUES (?, ?, ?)',
        [title, category || 'general', source || 'manual'],
        function(err) {
          if (err) {
            console.error('保存主题失败:', err);
            reject(err);
          } else {
            resolve({ id: this.lastID, success: true });
          }
        }
      );
    });
  }

  getTopics(limit = 20) {
    return new Promise((resolve, reject) => {
      this.db.all(
        'SELECT * FROM topics ORDER BY created_at DESC LIMIT ?',
        [limit],
        (err, rows) => {
          if (err) {
            console.error('获取主题失败:', err);
            reject(err);
          } else {
            resolve(rows);
          }
        }
      );
    });
  }

  incrementTopicUsage(topicId) {
    return new Promise((resolve, reject) => {
      this.db.run(
        'UPDATE topics SET used_count = used_count + 1 WHERE id = ?',
        [topicId],
        function(err) {
          if (err) {
            console.error('更新主题使用次数失败:', err);
            reject(err);
          } else {
            resolve({ success: true });
          }
        }
      );
    });
  }

  // 设置相关方法
  getSettings() {
    return new Promise((resolve, reject) => {
      this.db.all(
        'SELECT key, value FROM settings',
        (err, rows) => {
          if (err) {
            console.error('获取设置失败:', err);
            reject(err);
          } else {
            const settings = {};
            rows.forEach(row => {
              settings[row.key] = row.value;
            });
            resolve(settings);
          }
        }
      );
    });
  }

  saveSettings(settings) {
    return new Promise((resolve, reject) => {
      const entries = Object.entries(settings);
      let completed = 0;
      let hasError = false;

      entries.forEach(([key, value]) => {
        this.db.run(
          'INSERT OR REPLACE INTO settings (key, value, updated_at) VALUES (?, ?, CURRENT_TIMESTAMP)',
          [key, value],
          (err) => {
            if (err && !hasError) {
              hasError = true;
              console.error('保存设置失败:', err);
              reject(err);
            } else {
              completed++;
              if (completed === entries.length && !hasError) {
                resolve({ success: true });
              }
            }
          }
        );
      });
    });
  }

  // 模板相关方法
  saveTemplate(template) {
    return new Promise((resolve, reject) => {
      const { name, content, type } = template;

      this.db.run(
        'INSERT INTO templates (name, content, type) VALUES (?, ?, ?)',
        [name, content, type || 'article'],
        function(err) {
          if (err) {
            console.error('保存模板失败:', err);
            reject(err);
          } else {
            resolve({ id: this.lastID, success: true });
          }
        }
      );
    });
  }

  getTemplates(type = null) {
    return new Promise((resolve, reject) => {
      let sql = 'SELECT * FROM templates';
      let params = [];

      if (type) {
        sql += ' WHERE type = ?';
        params.push(type);
      }

      sql += ' ORDER BY created_at DESC';

      this.db.all(sql, params, (err, rows) => {
        if (err) {
          console.error('获取模板失败:', err);
          reject(err);
        } else {
          resolve(rows);
        }
      });
    });
  }

  close() {
    if (this.db) {
      this.db.close((err) => {
        if (err) {
          console.error('关闭数据库失败:', err);
        } else {
          console.log('数据库连接已关闭');
        }
      });
    }
  }
}

module.exports = DatabaseManager;